{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ab8ce77d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "astr=\"\"\"\n",
    "{\"country\": \"GA\", \"source_game_id\": \"3862927\", \"adrequest_count\": 1, \"placement\": null, \"revenue_sum\": 0.1, \"start_count\": 0, \"available_sum\": 1, \"platform\": \"android\", \"view_count\": 0, \"source_name\": \"SHAREit\", \"timestamp\": \"2022-07-11T00:00:00.000Z\"}\n",
    "\n",
    "\"\"\"\n",
    "adict=json.loads(astr.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c37399b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ",country    STRING\n",
      ",source_game_id    STRING\n",
      ",adrequest_count    BIGINT\n",
      ",placement    STRING\n",
      ",revenue_sum    double\n",
      ",start_count    BIGINT\n",
      ",available_sum    BIGINT\n",
      ",platform    STRING\n",
      ",view_count    BIGINT\n",
      ",source_name    STRING\n",
      ",timestamp    STRING\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cols_str=''\n",
    "cols_fmt=',{}    {}\\n'\n",
    "for item in adict.items():\n",
    "    hive_col_name=item[0]\n",
    "    if hive_col_name in ['date','day','size','zone']:\n",
    "        hive_col_name='`{}`'.format(hive_col_name)\n",
    "    hive_data_type='STRING'\n",
    "    if type(item[1]) is str:\n",
    "        pass\n",
    "    elif type(item[1]) is int:\n",
    "        hive_data_type='BIGINT'\n",
    "    elif type(item[1]) is float:\n",
    "        hive_data_type='float'\n",
    "    else:\n",
    "        pass\n",
    "    #hive_col_comment=item[1]\n",
    "    cols_str=cols_str+cols_fmt.format(hive_col_name,hive_data_type)\n",
    "print(cols_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3ebc8c20",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "create external table ads_dmp.dwb_ads_reporting_mintegral(\n",
      "`date`    BIGINT\n",
      ",app_id    BIGINT\n",
      ",placement_id    BIGINT\n",
      ",unit_id    BIGINT\n",
      ",platform    STRING\n",
      ",bidding_type    STRING\n",
      ",filled    BIGINT\n",
      ",request    BIGINT\n",
      ",impression    BIGINT\n",
      ",click    BIGINT\n",
      ",est_revenue    BIGINT\n",
      ",hb_load    BIGINT\n",
      ",hb_load_filled    BIGINT\n",
      ",fill_rate    BIGINT\n",
      ",country    STRING\n",
      ",ecpm    BIGINT\n",
      ",ctr    double\n",
      ",app_name    STRING\n",
      ",app_package    STRING\n",
      ",placement_name    STRING\n",
      ",ad_format    STRING\n",
      ",unit_name    STRING\n",
      ")\n",
      "partitioned by(\n",
      "  dt       string,\n",
      "  src_pt  string comment '备用分区字段')\n",
      "ROW FORMAT SERDE\n",
      "'org.apache.hive.hcatalog.data.JsonSerDe'\n",
      "STORED AS TEXTFILE\n",
      "LOCATION\n",
      "  's3://shareit.ads.ap-southeast-1/dw/table/dwb/ads_dmp/dwb_ads_reporting_mintegral/';\n",
      "\n"
     ]
    }
   ],
   "source": [
    "bottom_str=\"\"\"\n",
    ")\n",
    "partitioned by(\n",
    "  dt       string,\n",
    "  src_pt  string comment '备用分区字段')\n",
    "ROW FORMAT SERDE\n",
    "'org.apache.hive.hcatalog.data.JsonSerDe'\n",
    "STORED AS TEXTFILE\n",
    "LOCATION\n",
    "  's3://shareit.ads.ap-southeast-1/dw/table/dwb/ads_dmp/dwb_ads_reporting_mintegral/';\n",
    "\"\"\"\n",
    "head_str=\"\"\"\n",
    "create external table ads_dmp.dwb_ads_reporting_mintegral(\n",
    "\"\"\"\n",
    "print(head_str+cols_str[1:-1]+bottom_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12d69722",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
